package com.aaa.book_mall.dao.impl;

import com.aaa.book_mall.dao.BookDao;
import com.aaa.book_mall.entity.Book;
import com.aaa.book_mall.util.JDBCUtil;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import com.aaa.book_mall.entity.Audit;

import java.util.List;
import java.util.Map;

/**
 * @Description: BookDao的实现类
 * @ClassName: BookDaoImpl
 * @Author: fengyun
 * @Date: 2021/1/19 18:38
 * @Version: 1.0
 */
@Repository
public class BookDaoImpl implements BookDao {
    @Override
    public List<Book> queryAllBook(Book book) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT tb.bookid,tb.bookname,tb.author,tb.count,tb.remark,tb.typeid,tb.publisher,tb.price,tb.`status`,tb.selling,tt.typename from t_book tb inner JOIN t_type tt on tb.typeid=tt.typeid");
//      如果即选择了作者又选择了类型
        if(book.getAuthor()!=null && !"".equals(book.getAuthor()) && book.getTypeid()!=null){
            sql.append(" where tb.author like '%"+book.getAuthor()+"%'");
            sql.append(" and tb.typeid like '%"+book.getTypeid()+"%'");
        }else if(book.getAuthor()!=null && !"".equals(book.getAuthor())){
            //        如果有选择作者，拼上该sql
            sql.append(" where tb.author like '%"+book.getAuthor()+"%'");
        }else if(book.getTypeid()!=null){
//        如果有选择类型，拼上该sql
            sql.append(" where tb.typeid like '%"+book.getTypeid()+"%'");
        }

        sql.append(" limit ?,?");
        System.out.println(sql);

        List<List> lists = JDBCUtil.queryList(sql.toString(),(book.getPage()-1)*book.getLimit(),book.getLimit());
        ArrayList<Book> books = new ArrayList<>();
        if(lists.size()>0){
            for (List list : lists) {
                Book newbook=new Book((Integer) list.get(0),(String)list.get(1),(String)list.get(2),(Integer)list.get(3),(String)list.get(4),
                        (Integer)list.get(5),(String)list.get(6),(Double)list.get(7),(Integer)list.get(8),(Double)list.get(9),(String)list.get(10)
                        );
                books.add(newbook);
            }
        }
        return books;
    }

    @Override
    public List<Book> showBook() {
        String sql = "select * from t_book";
        List<List> lists = JDBCUtil.queryList(sql);
        ArrayList<Book> books = new ArrayList<>();
        if (lists.size() > 0) {
            for (List list : lists) {
                Book book=new Book((Integer) list.get(0),(String)list.get(1),(String)list.get(2),(Integer)list.get(3),(String)list.get(4),
                        (Integer)list.get(5),(String)list.get(6),(Double)list.get(7),(Integer)list.get(8),(Double)list.get(9),(String)list.get(10)
                );
                books.add(book);
            }
        }
        return books;
    }

    /**
     * 根据图书名称查询信息
     * @param bookname
     * @return
     */
    @Override
    public Book showByBook(String bookname) {
        String sql = "select * from t_book where bookname=?";
        List<List> lists = JDBCUtil.queryList(sql,bookname);
        System.out.println(lists);
        if (lists.size() > 0) {
            for (List list : lists) {
                Book book=new Book((Integer) list.get(0),(String)list.get(1),(String)list.get(2),(Integer)list.get(3),(String)list.get(4),
                        (Integer)list.get(5),(String)list.get(6),(Double)list.get(7),(Integer)list.get(8),(Double)list.get(9),(String)list.get(10)

                );
              return book;
            }
        }
        return null;
    }

    /**
     * 查询展示在客户端的信息
     * @return
     */
    @Override
    public List<Map<String, Object>> showBook(Book book) {
        String sql ="SELECT bookname,author,remark,selling,imgurlCode from t_book";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql, book);
        if(maps.size()>0){
            return maps;
        }
        return null;
    }

    @Override
    public int allCount() {
        String sql="select count(*) from t_book";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    @Override
    public int selectCountByBookid(int bookid) {
        String sql="select count from t_book where bookid=?";
        List<List> lists = JDBCUtil.queryList(sql, bookid);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    @Override
    public int updateCountByBookid(int bookcount, int bookid) {
        String sql="update t_book set count=? where bookid=?";
        int i = JDBCUtil.update(sql, bookcount, bookid);
        return i;
    }

//    /////////////////////////////////////////////////////////

    @Override
    public List<Map<String, Object>> getAllBook(Book book) {
        StringBuilder sql = new StringBuilder("select tb.bookid,tb.bookname,tb.author,tt.typename,tb.count,tb.remark,tb.price,tb.selling,tb.status,tb.publisher,tb.imgurlCode from t_book tb join t_type tt on tb.typeid=tt.typeid");
        if(book.getBookname()!=null && !"".equals(book.getBookname())){
            sql.append(" WHERE tb.bookname LIKE '%"+book.getBookname()+"%'");
        }
        sql.append(" ORDER BY tb.bookid LIMIT ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(),(book.getPage()-1)*book.getLimit(),book.getLimit());
        if(maps.size()>0){
            return maps;
        }
        return null;
    }


    /**
     * 根据bookid查询进价
     * @param bookid
     * @return
     */
    @Override
    public int selectPriceByBookid(int bookid) {
        String sql = "select price from t_book where bookid=?";
        List<List> lists = JDBCUtil.queryList(sql, bookid);
        int price=0;
        if(lists.size()>0){
            price=Integer.parseInt(lists.get(0).get(0).toString());

        }
        return price;
    }

    @Override
    public int getBookCount(Book book) {
        String sql = "select count(*) from t_book";
        List<List> lists = JDBCUtil.queryList(sql);
        if(lists.size()>0){
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

    /**
     * 新增
     * @param book
     * @return
     */
    @Override
    public int insertBook(Book book) {
        String sql = "insert into t_book(bookid,bookname,author,count,remark,typeid,publisher,selling,imgurlCode) values(null,?,?,?,?,?,?,?,?)";
        int update = JDBCUtil.update(sql,book.getBookname(), book.getAuthor(), book.getCount(),book.getRemark(), book.getTypeid(), book.getPublisher(),book.getSelling(),book.getImgurlCode());
        return update;
    }

    /**
     * 修改
     * @param book
     * @return
     */
    @Override
    public int updateBook(Book book) {
        String sql = "update t_book set bookname=?,author=?,count=?,remark=?,typeid=?,publisher=?,selling=?,imgurlCode=? where bookid=?";
        int update = JDBCUtil.update(sql, book.getBookname(), book.getAuthor(), book.getCount(),book.getRemark(), book.getTypeid(), book.getPublisher(), book.getSelling(),book.getImgurlCode(),book.getBookid());
        return update;
    }

    /**
     * 删除
     * @param bookid
     * @return
     */
    @Override
    public int deleteBook(Integer bookid) {
        String sql = "delete from t_book where bookid=?";
        return JDBCUtil.update(sql, bookid);
    }

    @Override
    public int updateStatus(Integer bookid, Integer status) {
        String sql = "update t_book set status=? where bookid=?";
        int i;
        if(status==1){
            i = JDBCUtil.update(sql, 0, bookid);
        }else{
            i = JDBCUtil.update(sql,1,bookid);
        }
        return i;
    }

    @Override
    public  List<Map<String, Object>> getOneBook() {
        String sql = "select * from t_book";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql);
        if(maps.size()>0){
            return maps;
        }
        return null;
    }
}
